package com.lenovo.util;


import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class DBUtil {
    // 数据库地址  jdbc:mysql://服务器域名：端口号/数据库名称？参数
    public static final String url = "jdbc:mysql://127.0.0.1:3306/emp?useUnicode=true&characterEncoding=utf-8";
    // 用户名
    public static final String user = "root";
    //密码
    public static final String pwd = "123456";
    //数据库连接对象
    private static java.sql.Connection conn;
    //数据库命令执行对象
    private static java.sql.PreparedStatement pstmt;
    // 数据库返回结果
    private ResultSet rs;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 创建连接
    public static void getConnection() {
        if (conn == null) {
            try {
                conn = DriverManager.getConnection(url, user, pwd);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 创建查询基本方法
    public ResultSet executeQuery(String sql, List<Object> params) {
        getConnection();
        try {
            // 创建命令执行对象
            pstmt = conn.prepareStatement(sql);
            // select * from user where name=? and id=?
            // 执行sql
            if (params != null && params.size() > 0) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    // 新增，修改，删除
    public int executeUpdate(String sql,List<Object> params){
        int result = 0;
        getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            // 执行sql
            if (params != null && params.size() > 0) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(i + 1, params.get(i));
                }
            }
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            this.close();
        }
        return  result;
    }

    //释放资源
    public void close(){

        try {
            if (rs!=null){
                rs.close();
                rs = null;
            }

            if (pstmt != null){
                pstmt.close();
                pstmt = null;
            }

            if (conn != null){
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}